使用mysql_fdw访问MySQL数据

注意事项

对MySQL外表执行DML操作时（UPDATE、DELETE、INSERT），源数据库表结构必须以首列作为单一主键，或作为构成复合主键的一部分。执行数据查询（SELECT操作）不受此主键约束限制。

前提条件

• 已安装mysql_fdw插件。具体操作，请参见安装、升级与卸载插件。

• 已创建云原生数据仓库 AnalyticDB PostgreSQL 版数据库账号。具体操作，请参见创建数据库账号。

快速入门

使用mysql_fdw插件来访问远程MySQL上的数据，操作步骤如下：

1. 登录云原生数据仓库AnalyticDB PostgreSQL版控制台。单击目标实例ID，进入目标实例基本信息页面。

2. 单击右上角登录数据库，在弹出框登录实例页面填写数据库账号和数据库密码，单击登录。

3. 进入数据库，使用CREATE SERVER命令创建一个外部服务器，这个外部服务器表示需要访问的MySQL数据库。如下所示是一个简单的使用示例。

   -- 创建foreign server
   CREATE SERVER mysql_svr FOREIGN DATA WRAPPER mysql_fdw
     OPTIONS (host '127.0.0.1', port '3306');
   
   -- 给刚刚创建的mysql_svr赋予一个user mapping（指定用户名密码）
   CREATE USER MAPPING FOR public SERVER mysql_svr
     OPTIONS (username '$MySQL_USER_NAME', password '$MySQL_PASS');
   
   -- 使用mysql_svr这个foreign server创建外表
   CREATE FOREIGN TABLE test_foreign_table(
     c1 INT,
     c2 INT,
     c3 TEXT,
     c4 TEXT)
     SERVER mysql_svr OPTIONS(dbname 'mysql_fdw_database', table_name 'test_foreign_table');

   参数说明：

   • CREATE SERVER：创建一个外部服务器。这个外部服务器表示需要访问的MySQL数据库。

   • CREATE USER MAPPING：创建一个用户映射，指定需要访问的MySQL数据库使用的用户名和密码。

   • CREATE FOREIGN TABLE：创建一个外表。外表的列名必须匹配需要被访问的远程表。也可以使用IMPORT FOREIGN SCHEMA的方式来让mysql_fdw自动在云原生数据仓库 AnalyticDB PostgreSQL 版实例上创建需要访问的MySQL表的外表。

4. 对MySQL数据库进行查询数据、插入数据、新增数据和删除数据的操作（仅支持这些操作）。

语法

CREATE SERVER [IF NOT EXISTS] server_name [TYPE 'server_type'] [VERSION 'server_version']
    FOREIGN DATA WRAPPER fdw_name
    [OPTIONS([mpp_execute 'any | master | all segments' ], option 'value' [, ... ])]

CREATE USER MAPPING [ IF NOT EXISTS ] FOR { user_name | USER | CURRENT_USER | PUBLIC }
    SERVER server_name
    [ OPTIONS ( option 'value' [ , ... ] ) ]

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [
  { column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE collation ] [ column_constraint [ ... ] ]
    | table_constraint }
    [, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
  SERVER server_name
[ OPTIONS ( [ mpp_execute 'any | master | all segments' ], option 'value' [, ... ] ) ]

IMPORT FOREIGN SCHEMA remote_schema
    [ { LIMIT TO | EXCEPT } ( table_name [, ...] ) ]
    FROM SERVER server_name
    INTO local_schema
    [ OPTIONS ( option 'value' [, ... ] ) ]

兼容性

云原生数据仓库 AnalyticDB PostgreSQL 版的mysql_fdw插件兼容如下MySQL实例。

公有云RDS MySQL

自建MySQL

社区v5.7以下的版本不再维护，以v5.7及以上为主要版本。

PolarDB MySQL

功能特性和数据类型

功能特性

连接池

在同一会话中的所有查询都使用相同的MySQL数据库连接，而不是去创建一个新的MySQL连接。

WHERE从句下推

把外表的WHERE从句下推到MySQL上，把与外表相关的WHERE条件加在MySQL上执行，因此需要传输到云原生数据仓库 AnalyticDB PostgreSQL 版实例的行数会更少。

投影下推

不同于将一个表的所有数据都从MySQL中查询到云原生数据仓库 AnalyticDB PostgreSQL 版实例中，mysql_fdw只返回那些属于 SELECT目标表的列，这样可以降低传输到云原生数据仓库 AnalyticDB PostgreSQL 版实例的数据流量，提升性能。

PREPARED STATEMENT

SELECT查询使用PREPARED STATEMENT查询而不是简单的查询协议。

JOIN下推

同一MySQL数据库的两个外表之间的连接操作被下推到远端的MySQL数据库上执行，而不是先获取两个表的所有行并在云原生数据仓库 AnalyticDB PostgreSQL 版实例本地执行连接操作，从而提升了性能。

AGGREGATE下推

将聚合操作（AGGREGATE）下推到远端的MySQL数据库上执行，而不是获取所有行并在云原生数据仓库 AnalyticDB PostgreSQL 版实例本地进行聚合。这样做可以显著提升性能。

ORDER BY下推

将ORDER BY子句下推到远端的MySQL数据库上执行，可以从MySQL获得有序的结果集。在MySQL数据库对于NULL的行为与云原生数据仓库 AnalyticDB PostgreSQL 版相反。为了获得等效的结果，在每个ORDER BY表达式的开头添加“expression IS NULL”子句，MySQL执行正向排序后会把NULL放到最后一个，和云原生数据仓库 AnalyticDB PostgreSQL 版的行为保持一致。

LIMIT OFFSET下推

在远端的MySQL数据库上执行LIMIT和OFFSET操作，不需要把所有数据拿到云原生数据仓库 AnalyticDB PostgreSQL 版数据库，减少了云原生数据仓库 AnalyticDB PostgreSQL 版数据库和MySQL数据库之间的数据流量。

数据类型